{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import os\n",
    "import matplotlib.font_manager as fm\n",
    "import datetime\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei'] \n",
    "plt.rcParams['axes.unicode_minus'] = False "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取预处理过的数据\n",
    "original = pd.read_excel('./第二问预处理.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售日期</th>\n",
       "      <th>扫码销售时间</th>\n",
       "      <th>单品编码</th>\n",
       "      <th>辅助列</th>\n",
       "      <th>销量(千克)</th>\n",
       "      <th>销售单价(元/千克)</th>\n",
       "      <th>销售类型</th>\n",
       "      <th>是否打折销售</th>\n",
       "      <th>分类编码</th>\n",
       "      <th>分类名称</th>\n",
       "      <th>批发价格(元/千克)</th>\n",
       "      <th>损耗率(%)</th>\n",
       "      <th>成本加成定价(元/千克)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>09:15:07.924</td>\n",
       "      <td>102900005117056</td>\n",
       "      <td>44013-102900005117056</td>\n",
       "      <td>0.396</td>\n",
       "      <td>7.6</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010504</td>\n",
       "      <td>辣椒类</td>\n",
       "      <td>4.32</td>\n",
       "      <td>7.08</td>\n",
       "      <td>7.929161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>09:17:27.295</td>\n",
       "      <td>102900005115960</td>\n",
       "      <td>44013-102900005115960</td>\n",
       "      <td>0.849</td>\n",
       "      <td>3.2</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>2.10</td>\n",
       "      <td>22.27</td>\n",
       "      <td>3.801660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>09:17:33.905</td>\n",
       "      <td>102900005117056</td>\n",
       "      <td>44013-102900005117056</td>\n",
       "      <td>0.409</td>\n",
       "      <td>7.6</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010504</td>\n",
       "      <td>辣椒类</td>\n",
       "      <td>4.32</td>\n",
       "      <td>7.08</td>\n",
       "      <td>7.929161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>09:19:45.450</td>\n",
       "      <td>102900005115823</td>\n",
       "      <td>44013-102900005115823</td>\n",
       "      <td>0.421</td>\n",
       "      <td>10.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>7.03</td>\n",
       "      <td>14.43</td>\n",
       "      <td>11.185496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>09:20:23.686</td>\n",
       "      <td>102900005115908</td>\n",
       "      <td>44013-102900005115908</td>\n",
       "      <td>0.539</td>\n",
       "      <td>8.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010101</td>\n",
       "      <td>花叶类</td>\n",
       "      <td>4.60</td>\n",
       "      <td>13.70</td>\n",
       "      <td>8.730243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878498</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:13.264</td>\n",
       "      <td>102900005115250</td>\n",
       "      <td>45107-102900005115250</td>\n",
       "      <td>0.284</td>\n",
       "      <td>24.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010801</td>\n",
       "      <td>食用菌</td>\n",
       "      <td>15.60</td>\n",
       "      <td>10.80</td>\n",
       "      <td>25.888789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878499</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:14.358</td>\n",
       "      <td>102900011022764</td>\n",
       "      <td>45107-102900011022764</td>\n",
       "      <td>0.669</td>\n",
       "      <td>12.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010501</td>\n",
       "      <td>茄类</td>\n",
       "      <td>7.00</td>\n",
       "      <td>6.90</td>\n",
       "      <td>12.518797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878500</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:20.264</td>\n",
       "      <td>102900005115250</td>\n",
       "      <td>45107-102900005115250</td>\n",
       "      <td>0.125</td>\n",
       "      <td>24.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010801</td>\n",
       "      <td>食用菌</td>\n",
       "      <td>15.60</td>\n",
       "      <td>10.80</td>\n",
       "      <td>25.888789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878501</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:35:21.509</td>\n",
       "      <td>102900011016701</td>\n",
       "      <td>45107-102900011016701</td>\n",
       "      <td>0.252</td>\n",
       "      <td>5.2</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010504</td>\n",
       "      <td>辣椒类</td>\n",
       "      <td>3.63</td>\n",
       "      <td>5.70</td>\n",
       "      <td>5.419417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878502</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>21:40:48.248</td>\n",
       "      <td>102900011022764</td>\n",
       "      <td>45107-102900011022764</td>\n",
       "      <td>0.803</td>\n",
       "      <td>12.0</td>\n",
       "      <td>销售</td>\n",
       "      <td>否</td>\n",
       "      <td>1011010501</td>\n",
       "      <td>茄类</td>\n",
       "      <td>7.00</td>\n",
       "      <td>6.90</td>\n",
       "      <td>12.518797</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>878503 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             销售日期        扫码销售时间             单品编码                    辅助列  \\\n",
       "0      2020-07-01  09:15:07.924  102900005117056  44013-102900005117056   \n",
       "1      2020-07-01  09:17:27.295  102900005115960  44013-102900005115960   \n",
       "2      2020-07-01  09:17:33.905  102900005117056  44013-102900005117056   \n",
       "3      2020-07-01  09:19:45.450  102900005115823  44013-102900005115823   \n",
       "4      2020-07-01  09:20:23.686  102900005115908  44013-102900005115908   \n",
       "...           ...           ...              ...                    ...   \n",
       "878498 2023-06-30  21:35:13.264  102900005115250  45107-102900005115250   \n",
       "878499 2023-06-30  21:35:14.358  102900011022764  45107-102900011022764   \n",
       "878500 2023-06-30  21:35:20.264  102900005115250  45107-102900005115250   \n",
       "878501 2023-06-30  21:35:21.509  102900011016701  45107-102900011016701   \n",
       "878502 2023-06-30  21:40:48.248  102900011022764  45107-102900011022764   \n",
       "\n",
       "        销量(千克)  销售单价(元/千克) 销售类型 是否打折销售        分类编码 分类名称  批发价格(元/千克)  损耗率(%)  \\\n",
       "0        0.396         7.6   销售      否  1011010504  辣椒类        4.32    7.08   \n",
       "1        0.849         3.2   销售      否  1011010101  花叶类        2.10   22.27   \n",
       "2        0.409         7.6   销售      否  1011010504  辣椒类        4.32    7.08   \n",
       "3        0.421        10.0   销售      否  1011010101  花叶类        7.03   14.43   \n",
       "4        0.539         8.0   销售      否  1011010101  花叶类        4.60   13.70   \n",
       "...        ...         ...  ...    ...         ...  ...         ...     ...   \n",
       "878498   0.284        24.0   销售      否  1011010801  食用菌       15.60   10.80   \n",
       "878499   0.669        12.0   销售      否  1011010501   茄类        7.00    6.90   \n",
       "878500   0.125        24.0   销售      否  1011010801  食用菌       15.60   10.80   \n",
       "878501   0.252         5.2   销售      否  1011010504  辣椒类        3.63    5.70   \n",
       "878502   0.803        12.0   销售      否  1011010501   茄类        7.00    6.90   \n",
       "\n",
       "        成本加成定价(元/千克)  \n",
       "0           7.929161  \n",
       "1           3.801660  \n",
       "2           7.929161  \n",
       "3          11.185496  \n",
       "4           8.730243  \n",
       "...              ...  \n",
       "878498     25.888789  \n",
       "878499     12.518797  \n",
       "878500     25.888789  \n",
       "878501      5.419417  \n",
       "878502     12.518797  \n",
       "\n",
       "[878503 rows x 13 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['销售日期', '扫码销售时间', '单品编码', '辅助列', '销量(千克)', '销售单价(元/千克)', '销售类型',\n",
       "       '是否打折销售', '分类编码', '分类名称', '批发价格(元/千克)', '损耗率(%)', '成本加成定价(元/千克)'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "分类: 水生根茎类\n",
      "             销售日期             单品编码  销量(千克)   分类名称  成本加成定价(元/千克)\n",
      "683    2020-07-01  102900005118824   0.210  水生根茎类     12.335866\n",
      "738    2020-07-01  102900005118824   0.190  水生根茎类     12.335866\n",
      "560    2020-07-01  102900005118824   0.170  水生根茎类     12.335866\n",
      "512    2020-07-01  102900005118824   0.361  水生根茎类     12.335866\n",
      "518    2020-07-01  102900005118824   0.291  水生根茎类     12.335866\n",
      "...           ...              ...     ...    ...           ...\n",
      "878091 2023-06-30  102900011007969   0.262  水生根茎类     10.255208\n",
      "878100 2023-06-30  102900005116899   0.332  水生根茎类     16.608778\n",
      "878153 2023-06-30  102900005118824   0.295  水生根茎类     20.824700\n",
      "878152 2023-06-30  102900005116899   0.946  水生根茎类     16.608778\n",
      "878128 2023-06-30  102900005116899   0.470  水生根茎类     16.608778\n",
      "\n",
      "[58647 rows x 5 columns]\n",
      "分类: 花叶类\n",
      "             销售日期             单品编码  销量(千克) 分类名称  成本加成定价(元/千克)\n",
      "661    2020-07-01  102900005115779   0.664  花叶类      9.209204\n",
      "662    2020-07-01  102900005115984   0.251  花叶类      6.505407\n",
      "664    2020-07-01  102900005115779   0.476  花叶类      9.209204\n",
      "665    2020-07-01  102900005115779   0.451  花叶类      9.209204\n",
      "671    2020-07-01  102900011008522   0.451  花叶类      8.462286\n",
      "...           ...              ...     ...  ...           ...\n",
      "878123 2023-06-30  102900005115786   0.357  花叶类      3.739002\n",
      "878121 2023-06-30  102900011030059   1.000  花叶类      6.163373\n",
      "878132 2023-06-30  102900011030097   1.000  花叶类      4.795696\n",
      "878133 2023-06-30  102900005115786   0.327  花叶类      3.739002\n",
      "878130 2023-06-30  102900005115946   0.336  花叶类      6.256989\n",
      "\n",
      "[331968 rows x 5 columns]\n",
      "分类: 花菜类\n",
      "             销售日期             单品编码  销量(千克) 分类名称  成本加成定价(元/千克)\n",
      "673    2020-07-01  102900011009970   0.780  花菜类     10.240316\n",
      "674    2020-07-01  102900005116714   0.336  花菜类     14.941920\n",
      "644    2020-07-01  102900011009970   0.535  花菜类     10.240316\n",
      "649    2020-07-01  102900005116714   0.386  花菜类     14.941920\n",
      "651    2020-07-01  102900005116714   0.458  花菜类     14.941920\n",
      "...           ...              ...     ...  ...           ...\n",
      "878125 2023-06-30  102900005116714   0.323  花菜类     11.974558\n",
      "878127 2023-06-30  102900005116714   0.385  花菜类     11.974558\n",
      "878134 2023-06-30  102900005116714   0.520  花菜类     11.974558\n",
      "878135 2023-06-30  102900011034026   0.755  花菜类     13.873553\n",
      "878136 2023-06-30  102900005116714   0.556  花菜类     11.974558\n",
      "\n",
      "[86570 rows x 5 columns]\n",
      "分类: 茄类\n",
      "             销售日期             单品编码  销量(千克) 分类名称  成本加成定价(元/千克)\n",
      "680    2020-07-01  102900005116509   0.628   茄类      4.085970\n",
      "681    2020-07-01  102900005116257   0.368   茄类      6.423924\n",
      "658    2020-07-01  102900005116257   0.368   茄类      6.423924\n",
      "636    2020-07-01  102900005116257   0.301   茄类      6.423924\n",
      "642    2020-07-01  102900005116509   0.678   茄类      4.085970\n",
      "...           ...              ...     ...  ...           ...\n",
      "878144 2023-06-30  102900005116257   0.503   茄类      6.221655\n",
      "878141 2023-06-30  102900011022764   0.318   茄类     12.518797\n",
      "878140 2023-06-30  102900005116257   0.671   茄类      6.221655\n",
      "878131 2023-06-30  102900011022764   0.410   茄类     12.518797\n",
      "878502 2023-06-30  102900011022764   0.803   茄类     12.518797\n",
      "\n",
      "[44898 rows x 5 columns]\n",
      "分类: 辣椒类\n",
      "             销售日期             单品编码  销量(千克) 分类名称  成本加成定价(元/千克)\n",
      "0      2020-07-01  102900005117056   0.396  辣椒类      7.929161\n",
      "660    2020-07-01  102900005125808   0.116  辣椒类     26.553197\n",
      "667    2020-07-01  102900005125808   0.043  辣椒类     26.553197\n",
      "668    2020-07-01  102900051004294   0.050  辣椒类     14.576117\n",
      "669    2020-07-01  102900005125808   0.085  辣椒类     26.553197\n",
      "...           ...              ...     ...  ...           ...\n",
      "878149 2023-06-30  102900011000328   0.418  辣椒类     13.016640\n",
      "878147 2023-06-30  102900011031100   1.000  辣椒类      6.019690\n",
      "878145 2023-06-30  102900011000328   0.243  辣椒类     13.016640\n",
      "878137 2023-06-30  102900011016701   0.388  辣椒类      5.419417\n",
      "878129 2023-06-30  102900011016701   0.136  辣椒类      5.419417\n",
      "\n",
      "[207996 rows x 5 columns]\n",
      "分类: 食用菌\n",
      "             销售日期             单品编码  销量(千克) 分类名称  成本加成定价(元/千克)\n",
      "663    2020-07-01  102900005116530   0.329  食用菌     19.731910\n",
      "666    2020-07-01  102900005116530   0.150  食用菌     19.731910\n",
      "682    2020-07-01  102900005116530   0.039  食用菌     19.731910\n",
      "629    2020-07-01  102900005116530   0.206  食用菌     19.731910\n",
      "638    2020-07-01  102900005116530   0.123  食用菌     19.731910\n",
      "...           ...              ...     ...  ...           ...\n",
      "878204 2023-06-30  102900005115250   0.267  食用菌     25.888789\n",
      "878114 2023-06-30  102900005115250   0.096  食用菌     25.888789\n",
      "878148 2023-06-30  102900005115250   0.121  食用菌     25.888789\n",
      "878138 2023-06-30  102900011034330   1.000  食用菌      5.506834\n",
      "878126 2023-06-30  102900005115250   0.129  食用菌     25.888789\n",
      "\n",
      "[148424 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "# 删除指定列\n",
    "original.drop(columns=['扫码销售时间', '辅助列', '销售单价(元/千克)', '销售类型', '是否打折销售', '分类编码', '批发价格(元/千克)', '损耗率(%)'], inplace=True)\n",
    "\n",
    "# 按照时间顺序排序\n",
    "original.sort_values('销售日期', inplace=True)\n",
    "\n",
    "# 按照分类名称和日期进行分组，计算销售总量\n",
    "grouped = original.groupby('分类名称')\n",
    "\n",
    "# 遍历每个分组并保留其他列的数据\n",
    "for name, group in grouped:\n",
    "# 打印每个分组的数据\n",
    "    print(f\"分类: {name}\")\n",
    "    print(group)\n",
    "# 保存蔬菜品类分组结果\n",
    "    group.to_excel(f\"./results/蔬菜品类/{name}.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取预处理过的数据\n",
    "huacai = pd.read_excel('./results/蔬菜品类/花菜类.xlsx')\n",
    "huaye = pd.read_excel('./results/蔬菜品类/花叶类.xlsx')\n",
    "qielei = pd.read_excel('./results/蔬菜品类/茄类.xlsx')\n",
    "lajiaolei = pd.read_excel('./results/蔬菜品类/辣椒类.xlsx')\n",
    "shiyongjun= pd.read_excel('./results/蔬菜品类/食用菌.xlsx')\n",
    "shuishenggenjing = pd.read_excel('./results/蔬菜品类/水生根茎类.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "46.64000000000001\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def calculate_daily_sales(dataframe, date):\n",
    "    # 将日期列转换为日期类型\n",
    "    dataframe['销售日期'] = pd.to_datetime(dataframe['销售日期'])\n",
    "    \n",
    "    # 根据日期分组并计算总销量\n",
    "    daily_sales = dataframe[dataframe['销售日期'] == date]['销量(千克)'].sum()\n",
    "    \n",
    "    # 返回当天的总销量\n",
    "    return daily_sales\n",
    "\n",
    "# 调用函数计算当天的总销量 检验花菜类2020年7月1日的销量是否为46.64\n",
    "daily_sales = calculate_daily_sales(huacai, '2020-7-1')\n",
    "print(daily_sales)\n",
    "#TRUE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 花菜类\n",
    "# 将日期列转换为日期类型\n",
    "huacai['销售日期'] = pd.to_datetime(huacai['销售日期'])\n",
    "\n",
    "# 按照时间顺序排序\n",
    "huacai.sort_values('销售日期', inplace=True)\n",
    "\n",
    "# 分别求每天的成本加成定价-第一步 加总\n",
    "huacai['成本加成定价*单次销量'] = huacai['成本加成定价(元/千克)'] * huacai['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "huacai = huacai.groupby('销售日期').agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "# 分别求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "huacai['成本加成定价'] = huacai['成本加成定价*单次销量'] / huacai['销量(千克)']\n",
    "\n",
    "# 删除多余项\n",
    "huacai.drop(columns=['成本加成定价*单次销量'], inplace=True)\n",
    "\n",
    "# 导出到Excel\n",
    "huacai.to_excel('./results/问题二/花菜类求加权成本加成定价.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 花叶类\n",
    "# 将日期列转换为日期类型\n",
    "huaye['销售日期'] = pd.to_datetime(huaye['销售日期'])\n",
    "\n",
    "# 按照时间顺序排序\n",
    "huaye.sort_values('销售日期', inplace=True)\n",
    "\n",
    "#分别求每天的成本加成定价-第一步 加总\n",
    "huaye['成本加成定价*单次销量'] = huaye['成本加成定价(元/千克)'] * huaye['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "huaye = huaye.groupby('销售日期').agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "# 分别求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "huaye['成本加成定价'] = huaye['成本加成定价*单次销量'] / huaye['销量(千克)']\n",
    "\n",
    "# 删除多余项\n",
    "huaye.drop(columns=['成本加成定价*单次销量'], inplace=True)\n",
    "\n",
    "# 导出到Excel\n",
    "huaye.to_excel('./results/问题二/花叶类求加权成本加成定价.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 茄类\n",
    "# 将日期列转换为日期类型\n",
    "qielei['销售日期'] = pd.to_datetime(qielei['销售日期'])\n",
    "\n",
    "# 按照时间顺序排序\n",
    "qielei.sort_values('销售日期', inplace=True)\n",
    "\n",
    "#分别求每天的成本加成定价-第一步 加总\n",
    "qielei['成本加成定价*单次销量'] = qielei['成本加成定价(元/千克)'] * qielei['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "qielei = qielei.groupby('销售日期').agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "# 分别求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "qielei['成本加成定价'] = qielei['成本加成定价*单次销量'] / qielei['销量(千克)']\n",
    "\n",
    "# 删除多余项\n",
    "qielei.drop(columns=['成本加成定价*单次销量'], inplace=True)\n",
    "\n",
    "# 导出到Excel\n",
    "qielei.to_excel('./results/问题二/茄类求加权成本加成定价.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 辣椒类\n",
    "# 将日期列转换为日期类型\n",
    "lajiaolei['销售日期'] = pd.to_datetime(lajiaolei['销售日期'])\n",
    "\n",
    "# 按照时间顺序排序\n",
    "lajiaolei.sort_values('销售日期', inplace=True)\n",
    "\n",
    "# 分别求每天的成本加成定价-第一步 加总\n",
    "lajiaolei['成本加成定价*单次销量'] = lajiaolei['成本加成定价(元/千克)'] * lajiaolei['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "lajiaolei = lajiaolei.groupby('销售日期').agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "# 分别求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "lajiaolei['成本加成定价'] = lajiaolei['成本加成定价*单次销量'] / lajiaolei['销量(千克)']\n",
    "\n",
    "# 删除多余项\n",
    "lajiaolei.drop(columns=['成本加成定价*单次销量'], inplace=True)\n",
    "\n",
    "# 导出到Excel\n",
    "lajiaolei.to_excel('./results/问题二/辣椒类求加权成本加成定价.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 食用菌\n",
    "# 将日期列转换为日期类型\n",
    "shiyongjun['销售日期'] = pd.to_datetime(shiyongjun['销售日期'])\n",
    "\n",
    "# 按照时间顺序排序\n",
    "shiyongjun.sort_values('销售日期', inplace=True)\n",
    "\n",
    "# 分别求每天的成本加成定价-第一步 加总\n",
    "shiyongjun['成本加成定价*单次销量'] = shiyongjun['成本加成定价(元/千克)'] * shiyongjun['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "shiyongjun = shiyongjun.groupby('销售日期').agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "# 分别求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "shiyongjun['成本加成定价'] = shiyongjun['成本加成定价*单次销量'] / shiyongjun['销量(千克)']\n",
    "\n",
    "# 删除多余项\n",
    "shiyongjun.drop(columns=['成本加成定价*单次销量'], inplace=True)\n",
    "\n",
    "# 导出到Excel\n",
    "shiyongjun.to_excel('./results/问题二/食用菌求加权成本加成定价.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 水生根茎类\n",
    "# 将日期列转换为日期类型\n",
    "shuishenggenjing['销售日期'] = pd.to_datetime(shuishenggenjing['销售日期'])\n",
    "\n",
    "# 按照时间顺序排序\n",
    "shuishenggenjing.sort_values('销售日期', inplace=True)\n",
    "\n",
    "# 分别求每天的成本加成定价-第一步 加总\n",
    "shuishenggenjing['成本加成定价*单次销量'] = shuishenggenjing['成本加成定价(元/千克)'] * shuishenggenjing['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "shuishenggenjing = shuishenggenjing.groupby('销售日期').agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "# 分别求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "shuishenggenjing['成本加成定价'] = shuishenggenjing['成本加成定价*单次销量'] / shuishenggenjing['销量(千克)']\n",
    "\n",
    "# 删除多余项\n",
    "shuishenggenjing.drop(columns=['成本加成定价*单次销量'], inplace=True)\n",
    "\n",
    "# 导出到Excel\n",
    "shuishenggenjing.to_excel('./results/问题二/水生根茎类求加权成本加成定价.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
